﻿-- Examples for queries that exercise different SQL objects implemented by this assembly

-----------------------------------------------------------------------------------------
-- Stored procedure
-----------------------------------------------------------------------------------------
-- exec StoredProcedureName


-----------------------------------------------------------------------------------------
-- User defined function
-----------------------------------------------------------------------------------------
-- select dbo.FunctionName()


-----------------------------------------------------------------------------------------
-- User defined type
-----------------------------------------------------------------------------------------
-- CREATE TABLE test_table (col1 UserType)
-- go
--
-- INSERT INTO test_table VALUES (convert(uri, 'Instantiation String 1'))
-- INSERT INTO test_table VALUES (convert(uri, 'Instantiation String 2'))
-- INSERT INTO test_table VALUES (convert(uri, 'Instantiation String 3'))
--
-- select col1::method1() from test_table



-----------------------------------------------------------------------------------------
-- User defined type
-----------------------------------------------------------------------------------------
-- select dbo.AggregateName(Column1) from Table1


SELECT TOP 10 [ID]
      ,[CREATEDATE]
      , 'Original' = CAST([PATIENTDOCUMENT] AS XML)
      , 'Compress' = CAST(CAST(Lib.clr.fn_decompress(Lib.clr.fn_compress(CAST([PATIENTDOCUMENT] AS VARBINARY(MAX)))) AS VARCHAR(MAX)) AS XML)
      --, 'TEST' = DIFFERENCE([PATIENTDOCUMENT], CAST(Lib.clr.fn_decompress(Lib.clr.fn_compress(CAST([PATIENTDOCUMENT] AS VARBINARY(MAX)))) AS VARCHAR(MAX)))
      ,[TEMP]
      ,[BATCHID]
      ,[userid]
  FROM [Stage].[dbo].[D1_CLINICALDOCUMENT_PATIENT]